package com.ubst.smartad.impl;

import java.sql.*;
import java.util.*;
import java.io.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.ubst.smartad.dao.TSASystemDO;
import com.ubst.smartad.helper.CryptHelper;
import com.ubst.smartad.common.TimeUtil;
import com.ubst.smartad.logger.Debug;

public class TSASystemMngImpl {
	
	Connection con = null;
	private CryptHelper chelper;
	private String serverName;
	private Debug debug;

	/** 
     * 생성자
     *   
     *   
    */ 
	public TSASystemMngImpl(Connection con, String serverName, String log_level) {
		this.con = con;
		this.chelper = new CryptHelper();
		this.serverName = serverName;
		this.debug = new Debug("TSASystemMngImpl", log_level);

	}
	
    /** 
	 * @author KJH
     * 시스템 정보 등록
     *   
     */  
    public void insertSystem(TSASystemDO systemDO) throws SQLException{
        PreparedStatement pstmt=null;

        StringBuffer queryBuf = null;
        try {

			long l_cur_date = TimeUtil.getCurrentTime();

            queryBuf = new StringBuffer();
			queryBuf.append("INSERT INTO TSA_SYSTEM " )
				.append(" ( ")
				.append("  SYSTEMID ,GROUPID ,SYSTEMNAME ,WINDOWSNAME ,SYSADDRESS ")
				.append(" ,OSINFO ,CONNPROTO ,CONNPORT ,TRANSPROTO ,TRANSPORT ")
				.append(" ,CONNACCOUNT ,CONNPASS ,SECPASS ,NSTATUS ,CREATEDATE ")
				.append(" ) ")
				.append(" VALUES")
				.append(" ( ") 
				.append("  ? ,? ,? ,? ,? ")
				.append(" ,? ,? ,? ,? ,? ")
				.append(" ,? ,? ,? ,? ,? ")
				.append(" ) ");

			debug.debug("insertSystem ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, systemDO.systemid);
			pstmt.setString(idx++, systemDO.groupid);
			pstmt.setString(idx++, systemDO.systemname);
			pstmt.setString(idx++, systemDO.windowsname);
			pstmt.setString(idx++, systemDO.sysaddress);
			pstmt.setString(idx++, systemDO.osinfo);
			pstmt.setString(idx++, systemDO.connproto);
			pstmt.setInt(idx++, systemDO.connport);
			pstmt.setString(idx++, systemDO.transproto);
			pstmt.setInt(idx++, systemDO.transport);
			pstmt.setString(idx++, systemDO.connaccount);
			pstmt.setString(idx++, chelper.encrypt(systemDO.connpass));
			pstmt.setString(idx++, chelper.encrypt(systemDO.secpass));
			pstmt.setInt(idx++, systemDO.nstatus);
			pstmt.setString(idx++, TimeUtil.getDateString(l_cur_date, "yyyyMMddHHmmss"));

            pstmt.execute();
            pstmt.close();
			queryBuf = null;

        }catch (SQLException e){
			throw e;
        }finally{
            if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
        }

    }

    /** 
	 * @author KJH
     * 시스템 그룹정보 변경
     *   
     */  
    public void updateSystem(TSASystemDO systemDO) throws SQLException{
        PreparedStatement pstmt=null;

        StringBuffer queryBuf = null;
        try {

			long l_cur_date = TimeUtil.getCurrentTime();

            queryBuf = new StringBuffer();
			queryBuf.append("UPDATE TSA_SYSTEM " )
				.append(" SET ")
				.append("  GROUPID=? ,SYSTEMNAME=? ,WINDOWSNAME=? ,SYSADDRESS=? ,OSINFO=? ")
				.append(" ,CONNPROTO=? ,CONNPORT=? ,TRANSPROTO=? ,TRANSPORT=? ,CONNACCOUNT=? ")
				.append(" ,NSTATUS=? ");
			if(systemDO.connpass!=null && !systemDO.connpass.trim().equals(""))
			{
				queryBuf.append(" ,CONNPASS=? ");
			}
			if(systemDO.secpass!=null && !systemDO.secpass.trim().equals(""))
			{
				queryBuf.append(" ,SECPASS=? ");
			}

			queryBuf.append(" WHERE SYSTEMID=? ");

			debug.debug("updateSystem ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, systemDO.groupid);
			pstmt.setString(idx++, systemDO.systemname);
			pstmt.setString(idx++, systemDO.windowsname);
			pstmt.setString(idx++, systemDO.sysaddress);
			pstmt.setString(idx++, systemDO.osinfo);
			pstmt.setString(idx++, systemDO.connproto);
			pstmt.setInt(idx++, systemDO.connport);
			pstmt.setString(idx++, systemDO.transproto);
			pstmt.setInt(idx++, systemDO.transport);
			pstmt.setString(idx++, systemDO.connaccount);
			pstmt.setInt(idx++, systemDO.nstatus);
			if(systemDO.connpass!=null && !systemDO.connpass.trim().equals(""))
			{
				pstmt.setString(idx++, chelper.encrypt(systemDO.connpass));
			}
			if(systemDO.secpass!=null && !systemDO.secpass.trim().equals(""))
			{
				pstmt.setString(idx++, chelper.encrypt(systemDO.secpass));
			}
			pstmt.setString(idx++, systemDO.systemid);

            pstmt.execute();
            pstmt.close();
			queryBuf = null;

        }catch (SQLException e){
			throw e;
        }finally{
            if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
        }

    }

	/**
	 * @author : KJH 
	 * 시스템 정보 출력
	 *
	 */
	public TSASystemDO selectSystem(String systemid) throws SQLException {

		PreparedStatement pstmt=null;
		ResultSet rs = null;

		TSASystemDO systemDO = null;

		try {
			
			StringBuffer queryBuf = new StringBuffer();
			queryBuf.append("SELECT ")
				.append("  GROUPID ,SYSTEMNAME ,WINDOWSNAME ,SYSADDRESS ,OSINFO ")
				.append(" ,CONNPROTO ,CONNPORT ,TRANSPROTO ,TRANSPORT ,CONNACCOUNT ")
				.append(" ,CONNPASS ,SECPASS ,NSTATUS ,CREATEDATE ")
				.append(" FROM TSA_SYSTEM ")
				.append(" WHERE SYSTEMID=? ");

			debug.debug("selectSystem ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			pstmt.setString(1, systemid);

			rs = pstmt.executeQuery();

			if ( rs.next() ){
				systemDO = new TSASystemDO();
				systemDO.systemid = systemid;
				systemDO.groupid = rs.getString("GROUPID");
				systemDO.systemname = rs.getString("SYSTEMNAME");
				systemDO.windowsname = rs.getString("WINDOWSNAME");
				systemDO.sysaddress = rs.getString("SYSADDRESS");
				systemDO.osinfo = rs.getString("OSINFO");
				systemDO.connproto = rs.getString("CONNPROTO");
				systemDO.connport = rs.getInt("CONNPORT");
				systemDO.transproto = rs.getString("TRANSPROTO");
				systemDO.transport = rs.getInt("TRANSPORT");
				systemDO.connaccount = rs.getString("CONNACCOUNT");
				//systemDO.connpass = rs.getString("CONNPASS");
				//systemDO.secpass = rs.getString("SECPASS");
				systemDO.nstatus = rs.getInt("NSTATUS");
				systemDO.createdate = rs.getString("CREATEDATE");
			}

			rs.close();
			pstmt.close();

		}catch (SQLException e){
			throw e;
		}finally{
			if ( rs != null) try{rs.close();}catch(SQLException ee){}
			if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
		}

		return systemDO;
	}

	/**
	 * @author : KJH 
	 * 그룹에 포함된 시스템 리스트 출력
	 *
	 */
	public Vector selectSystemList(String groupid) throws SQLException {

		PreparedStatement pstmt=null;
		ResultSet rs = null;

		Vector systemDOList = new Vector();
		TSASystemDO systemDO = null;

		try {
			
			StringBuffer queryBuf = new StringBuffer();
			queryBuf.append("SELECT ")
				.append("  SYSTEMID ,SYSTEMNAME ,WINDOWSNAME ,SYSADDRESS ,OSINFO ")
				.append(" ,CONNPROTO ,CONNPORT ,TRANSPROTO ,TRANSPORT ,CONNACCOUNT ")
				.append(" ,CONNPASS ,SECPASS ,NSTATUS ,CREATEDATE ")
				.append(" FROM TSA_SYSTEM ")
				.append(" WHERE GROUPID=? ");

			debug.debug("selectSystemList ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, groupid);

			rs = pstmt.executeQuery();

			while ( rs.next() ){
				systemDO = new TSASystemDO();
				systemDO.systemid = rs.getString("SYSTEMID");;
				systemDO.groupid = groupid;
				systemDO.systemname = rs.getString("SYSTEMNAME");
				systemDO.windowsname = rs.getString("WINDOWSNAME");
				systemDO.sysaddress = rs.getString("SYSADDRESS");
				systemDO.osinfo = rs.getString("OSINFO");
				systemDO.connproto = rs.getString("CONNPROTO");
				systemDO.connport = rs.getInt("CONNPORT");
				systemDO.transproto = rs.getString("TRANSPROTO");
				systemDO.transport = rs.getInt("TRANSPORT");
				systemDO.connaccount = rs.getString("CONNACCOUNT");
				//systemDO.connpass = rs.getString("CONNPASS");
				//systemDO.secpass = rs.getString("SECPASS");
				systemDO.nstatus = rs.getInt("NSTATUS");
				systemDO.createdate = rs.getString("CREATEDATE");

				systemDOList.add(systemDO);
				systemDO = null;
			}

			rs.close();
			pstmt.close();

		}catch (SQLException e){
			throw e;
		}finally{
			if ( rs != null) try{rs.close();}catch(SQLException ee){}
			if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
		}

		return systemDOList;
	}

    /** 
	 * @author KJH
     * 그룹에 포함된 시스템 삭제
     *   
     */  
    public void deleteSystemOfGroup(String groupid) throws SQLException{
        PreparedStatement pstmt=null;

        StringBuffer queryBuf = null;
        try {

            queryBuf = new StringBuffer();
			queryBuf.append("DELETE FROM TSA_SYSTEM " )
				.append(" WHERE GROUPID=? ");

			debug.debug("deleteSystemOfGroup ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, groupid);

            pstmt.execute();
            pstmt.close();
			queryBuf = null;

        }catch (SQLException e){
			throw e;
        }finally{
            if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
        }

    }

    /** 
	 * @author KJH
     * 시스템 삭제
     *   
     */  
    public void deleteSystem(String systemid) throws SQLException{
        PreparedStatement pstmt=null;

        StringBuffer queryBuf = null;
        try {

            queryBuf = new StringBuffer();
			queryBuf.append("DELETE FROM TSA_SYSTEM " )
				.append(" WHERE SYSTEMID=? ");

			debug.debug("deleteSystem ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, systemid);

            pstmt.execute();
            pstmt.close();
			queryBuf = null;

        }catch (SQLException e){
			throw e;
        }finally{
            if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
        }

    }

}
